package com.century.sjt.EMChat.db;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;

import com.century.sjt.Constant;
import com.century.sjt.SjtApplication;
import com.century.sjt.domain.InviteMessage;
import com.century.sjt.domain.InviteMessage.InviteMesageStatus;
import com.century.sjt.domain.RobotUser;
import com.century.sjt.entity.MyDraft;
import com.century.sjt.EMChat.domain.EaseUser;
import com.century.sjt.EMChat.domain.Translate;
import com.easemob.util.HanziToPinyin;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DemoDBManager {
    static private DemoDBManager dbMgr = new DemoDBManager();
    private DbOpenHelper dbHelper;
    
    private DemoDBManager(){
        dbHelper = DbOpenHelper.getInstance(SjtApplication.getInstance().getApplicationContext());
    }
    
    public static synchronized DemoDBManager getInstance(){
        if(dbMgr == null){
            dbMgr = new DemoDBManager();
    }
        return dbMgr;
    }
    
    /**
     * 保存好友list
     * 
     * @param contactList
     */
    synchronized public void saveContactList(List<EaseUser> contactList) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            db.delete(UserDao.TABLE_NAME, null, null);
            for (EaseUser user : contactList) {
                ContentValues values = new ContentValues();
                values.put(UserDao.COLUMN_NAME_ID, user.getUsername());
                if(user.getNick() != null)
                    values.put(UserDao.COLUMN_NAME_NICK, user.getNick());
                if(user.getAvatar() != null)
                    values.put(UserDao.COLUMN_NAME_AVATAR, user.getAvatar());
                if (user.getMobile() !=null)
                    values.put(UserDao.COLUMN_NAME_MOBILE,user.getMobile());
                if (user.getAvatarid() != null)
                    values.put(UserDao.COLUMN_NAME_AVATARID,user.getAvatarid());
                if (user.getIsShop()!=null)
                    values.put(UserDao.COLUMN_NAME_ISSHOP,user.getIsShop());
                if (user.getShopId()!=null)
                    values.put(UserDao.COLUMN_NAME_SHOPID,user.getIsShop());
                db.replace(UserDao.TABLE_NAME, null, values);
            }
        }
    }

    /**
     * 获取好友list
     * 
     * @return
     */
    synchronized public Map<String, EaseUser> getContactList() {
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Map<String, EaseUser> users = new HashMap<String, EaseUser>();
        if (db.isOpen()) {
            Cursor cursor = db.rawQuery("select * from " + UserDao.TABLE_NAME /* + " desc" */, null);
            while (cursor.moveToNext()) {
                String username = cursor.getString(cursor.getColumnIndex(UserDao.COLUMN_NAME_ID));
                String nick = cursor.getString(cursor.getColumnIndex(UserDao.COLUMN_NAME_NICK));
                String avatar = cursor.getString(cursor.getColumnIndex(UserDao.COLUMN_NAME_AVATAR));
                String mobile = cursor.getString(cursor.getColumnIndex(UserDao.COLUMN_NAME_MOBILE));
                String avatarid = cursor.getString(cursor.getColumnIndex(UserDao.COLUMN_NAME_AVATARID));
                String isshop = cursor.getString(cursor.getColumnIndex(UserDao.COLUMN_NAME_ISSHOP));
                String shopid = cursor.getString(cursor.getColumnIndex(UserDao.COLUMN_NAME_SHOPID));
                EaseUser user = new EaseUser(username);
                user.setNick(nick);
                user.setAvatar(avatar);
                user.setMobile(mobile);
                user.setAvatarid(avatarid);
                user.setIsShop(isshop);
                user.setShopId(shopid);
                String headerName = null;
                if (!TextUtils.isEmpty(user.getNick())) {
                    headerName = user.getNick();
                } else {
                    headerName = user.getUsername();
                }
                
                if (username.equals(Constant.NEW_FRIENDS_USERNAME) || username.equals(Constant.GROUP_USERNAME)
                        || username.equals(Constant.CHAT_ROOM)|| username.equals(Constant.CHAT_ROBOT)) {
                    user.setInitialLetter("");
                } else if (Character.isDigit(headerName.charAt(0))) {
                    user.setInitialLetter("#");
                } else {
                    user.setInitialLetter(HanziToPinyin.getInstance().get(headerName.substring(0, 1))
                            .get(0).target.substring(0, 1).toUpperCase());
                    char header = user.getInitialLetter().toLowerCase().charAt(0);
                    if (header < 'a' || header > 'z') {
                        user.setInitialLetter("#");
                    }
                }
                users.put(username, user);
            }
            cursor.close();
        }
        return users;
    }
    
    /**
     * 删除一个联系人
     * @param username
     */
    synchronized public void deleteContact(String username){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if(db.isOpen()){
            db.delete(UserDao.TABLE_NAME, UserDao.COLUMN_NAME_ID + " = ?", new String[]{username});
        }
    }
    
    /**
     * 保存一个联系人
     * @param user
     */
    synchronized public void saveContact(EaseUser user){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(UserDao.COLUMN_NAME_ID, user.getUsername());
        if(user.getNick() != null)
            values.put(UserDao.COLUMN_NAME_NICK, user.getNick());
        if(user.getAvatar() != null)
            values.put(UserDao.COLUMN_NAME_AVATAR, user.getAvatar());
        if (user.getMobile() !=null)
            values.put(UserDao.COLUMN_NAME_MOBILE,user.getMobile());
        if (user.getAvatarid() !=null)
            values.put(UserDao.COLUMN_NAME_AVATARID,user.getAvatarid());
        if (user.getIsShop() !=null)
            values.put(UserDao.COLUMN_NAME_ISSHOP,user.getIsShop());
        if (user.getShopId() !=null)
            values.put(UserDao.COLUMN_NAME_SHOPID,user.getIsShop());
        if(db.isOpen()){
            db.replace(UserDao.TABLE_NAME, null, values);
        }
    }
    
    public void setDisabledGroups(List<String> groups){
        setList(UserDao.COLUMN_NAME_DISABLED_GROUPS, groups);
    }
    
    public List<String> getDisabledGroups(){
        return getList(UserDao.COLUMN_NAME_DISABLED_GROUPS);
    }
    
    public void setDisabledIds(List<String> ids){
        setList(UserDao.COLUMN_NAME_DISABLED_IDS, ids);
    }
    
    public List<String> getDisabledIds(){
        return getList(UserDao.COLUMN_NAME_DISABLED_IDS);
    }
    
    synchronized private void setList(String column, List<String> strList){
        StringBuilder strBuilder = new StringBuilder();
        
        for(String hxid:strList){
            strBuilder.append(hxid).append("$");
        }
        
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            ContentValues values = new ContentValues();
            values.put(column, strBuilder.toString());

            db.update(UserDao.PREF_TABLE_NAME, values, null,null);
        }
    }
    
    synchronized private List<String> getList(String column){
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select " + column + " from " + UserDao.PREF_TABLE_NAME,null);
        if (!cursor.moveToFirst()) {
            cursor.close();
            return null;
        }

        String strVal = cursor.getString(0);
        if (strVal == null || strVal.equals("")) {
            return null;
        }
        
        cursor.close();
        
        String[] array = strVal.split("$");
        
        if(array != null && array.length > 0){
            List<String> list = new ArrayList<String>();
            for(String str:array){
                list.add(str);
            }
            
            return list;
        }
        
        return null;
    }
    
    /**
     * 保存message
     * @param message
     * @return  返回这条messaged在db中的id
     */
    public synchronized Integer saveMessage(InviteMessage message){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        int id = -1;
        if(db.isOpen()){
            ContentValues values = new ContentValues();
            values.put(InviteMessgeDao.COLUMN_NAME_FROM, message.getFrom());
            values.put(InviteMessgeDao.COLUMN_NAME_GROUP_ID, message.getGroupId());
            values.put(InviteMessgeDao.COLUMN_NAME_GROUP_Name, message.getGroupName());
            values.put(InviteMessgeDao.COLUMN_NAME_REASON, message.getReason());
            values.put(InviteMessgeDao.COLUMN_NAME_TIME, message.getTime());
            values.put(InviteMessgeDao.COLUMN_NAME_STATUS, message.getStatus().ordinal());
            db.insert(InviteMessgeDao.TABLE_NAME, null, values);

            Cursor cursor = db.rawQuery("select last_insert_rowid() from " + InviteMessgeDao.TABLE_NAME,null);
            if(cursor.moveToFirst()){
                id = cursor.getInt(0);
            }

            cursor.close();
        }
        return id;
    }

    /**
     * 更新message
     * @param msgId
     * @param values
     */
    synchronized public void updateMessage(int msgId,ContentValues values){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if(db.isOpen()){
            db.update(InviteMessgeDao.TABLE_NAME, values, InviteMessgeDao.COLUMN_NAME_ID + " = ?", new String[]{String.valueOf(msgId)});
        }
    }

    /**
     * 获取messges
     * @return
     */
    synchronized public List<InviteMessage> getMessagesList(){
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        List<InviteMessage> msgs = new ArrayList<InviteMessage>();
        if(db.isOpen()){
            Cursor cursor = db.rawQuery("select * from " + InviteMessgeDao.TABLE_NAME + " desc",null);
            while(cursor.moveToNext()){
                InviteMessage msg = new InviteMessage();
                int id = cursor.getInt(cursor.getColumnIndex(InviteMessgeDao.COLUMN_NAME_ID));
                String from = cursor.getString(cursor.getColumnIndex(InviteMessgeDao.COLUMN_NAME_FROM));
                String groupid = cursor.getString(cursor.getColumnIndex(InviteMessgeDao.COLUMN_NAME_GROUP_ID));
                String groupname = cursor.getString(cursor.getColumnIndex(InviteMessgeDao.COLUMN_NAME_GROUP_Name));
                String reason = cursor.getString(cursor.getColumnIndex(InviteMessgeDao.COLUMN_NAME_REASON));
                long time = cursor.getLong(cursor.getColumnIndex(InviteMessgeDao.COLUMN_NAME_TIME));
                int status = cursor.getInt(cursor.getColumnIndex(InviteMessgeDao.COLUMN_NAME_STATUS));

                msg.setId(id);
                msg.setFrom(from);
                msg.setGroupId(groupid);
                msg.setGroupName(groupname);
                msg.setReason(reason);
                msg.setTime(time);
                if(status == InviteMesageStatus.BEINVITEED.ordinal())
                    msg.setStatus(InviteMesageStatus.BEINVITEED);
                else if(status == InviteMesageStatus.BEAGREED.ordinal())
                    msg.setStatus(InviteMesageStatus.BEAGREED);
                else if(status == InviteMesageStatus.BEREFUSED.ordinal())
                    msg.setStatus(InviteMesageStatus.BEREFUSED);
                else if(status == InviteMesageStatus.AGREED.ordinal())
                    msg.setStatus(InviteMesageStatus.AGREED);
                else if(status == InviteMesageStatus.REFUSED.ordinal())
                    msg.setStatus(InviteMesageStatus.REFUSED);
                else if(status == InviteMesageStatus.BEAPPLYED.ordinal()){
                    msg.setStatus(InviteMesageStatus.BEAPPLYED);
                }
                msgs.add(msg);
            }
            cursor.close();
        }
        return msgs;
    }

    /**
     * 删除要求消息
     * @param from
     */
    synchronized public void deleteMessage(String from){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if(db.isOpen()){
            db.delete(InviteMessgeDao.TABLE_NAME, InviteMessgeDao.COLUMN_NAME_FROM + " = ?", new String[]{from});
        }
    }

    synchronized int getUnreadNotifyCount(){
        int count = 0;
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        if(db.isOpen()){
            Cursor cursor = db.rawQuery("select " + InviteMessgeDao.COLUMN_NAME_UNREAD_MSG_COUNT + " from " + InviteMessgeDao.TABLE_NAME, null);
            if(cursor.moveToFirst()){
                count = cursor.getInt(0);
            }
            cursor.close();
        }
        return count;
    }

    synchronized void setUnreadNotifyCount(int count){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if(db.isOpen()){
            ContentValues values = new ContentValues();
            values.put(InviteMessgeDao.COLUMN_NAME_UNREAD_MSG_COUNT, count);

            db.update(InviteMessgeDao.TABLE_NAME, values, null,null);
        }
    }

    /**
     * 保存draft
     * @param
     * @return  返回这条draft在db中的id
     */
    public synchronized Integer saveDraft(MyDraft message){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        int id = -1;
        if(db.isOpen()){
            ContentValues values = new ContentValues();
            values.put(DraftDao.COLUMN_NAME_FROM, message.getFrom());
            values.put(DraftDao.COLUMN_NAME_USERID, message.getUserId());
            values.put(DraftDao.COLUMN_NAME_MTEXT, message.getMtext());
            values.put(DraftDao.COLUMN_NAME_GROUP_ID, message.getGroupId());
            values.put(DraftDao.COLUMN_NAME_GROUP_Name, message.getGroupName());
            values.put(DraftDao.COLUMN_NAME_TIME, message.getTime());
            db.insert(DraftDao.TABLE_NAME, null, values);

            Cursor cursor = db.rawQuery("select last_insert_rowid() from " + DraftDao.TABLE_NAME,null);
            if(cursor.moveToFirst()){
                id = cursor.getInt(0);
            }

            cursor.close();
        }
        return id;
    }

    /**
     * 更新Draft
     * @param msgId
     * @param values
     */
    synchronized public void updateDraft(int msgId,ContentValues values){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if(db.isOpen()){
            db.update(DraftDao.TABLE_NAME, values, DraftDao.COLUMN_NAME_ID + " = ?", new String[]{String.valueOf(msgId)});
        }
    }

    /**
     * 获取Draft
     * @return
     */
    synchronized public MyDraft getDraft(String aUserId, String aFrom){
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        MyDraft msg = new MyDraft();
        if(db.isOpen()){
//            Cursor cursor = db.rawQuery("select * from " + DraftDao.TABLE_NAME + " desc", new String[]{aFrom});
            Cursor cursor = db.rawQuery(
                    "select * from " + DraftDao.TABLE_NAME + " where " + DraftDao.COLUMN_NAME_USERID + "=? and " + DraftDao.COLUMN_NAME_FROM + "=?",
                    new String[]{aUserId, aFrom});
            while(cursor.moveToNext()){
                int id = cursor.getInt(cursor.getColumnIndex(DraftDao.COLUMN_NAME_ID));
                String from = cursor.getString(cursor.getColumnIndex(DraftDao.COLUMN_NAME_FROM));
                String userid = cursor.getString(cursor.getColumnIndex(DraftDao.COLUMN_NAME_USERID));
                String mtext = cursor.getString(cursor.getColumnIndex(DraftDao.COLUMN_NAME_MTEXT));
                String groupid = cursor.getString(cursor.getColumnIndex(DraftDao.COLUMN_NAME_GROUP_ID));
                String groupname = cursor.getString(cursor.getColumnIndex(DraftDao.COLUMN_NAME_GROUP_Name));
                long time = cursor.getLong(cursor.getColumnIndex(DraftDao.COLUMN_NAME_TIME));

                msg.setId(id);
                msg.setFrom(from);
                msg.setUserId(userid);
                msg.setMtext(mtext);
                msg.setGroupId(groupid);
                msg.setGroupName(groupname);
                msg.setTime(time);


            }
            cursor.close();
        }
        return msg;
    }
    synchronized public List<MyDraft> getDraftList(){
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        List<MyDraft> msgs = new ArrayList<MyDraft>();
        if(db.isOpen()){
            Cursor cursor = db.rawQuery("select * from " + DraftDao.TABLE_NAME + " desc",null);
            while(cursor.moveToNext()){
                MyDraft msg = new MyDraft();
                int id = cursor.getInt(cursor.getColumnIndex(DraftDao.COLUMN_NAME_ID));
                String from = cursor.getString(cursor.getColumnIndex(DraftDao.COLUMN_NAME_FROM));
                String userid = cursor.getString(cursor.getColumnIndex(DraftDao.COLUMN_NAME_USERID));
                String mtext = cursor.getString(cursor.getColumnIndex(DraftDao.COLUMN_NAME_MTEXT));
                String groupid = cursor.getString(cursor.getColumnIndex(DraftDao.COLUMN_NAME_GROUP_ID));
                String groupname = cursor.getString(cursor.getColumnIndex(DraftDao.COLUMN_NAME_GROUP_Name));
                long time = cursor.getLong(cursor.getColumnIndex(DraftDao.COLUMN_NAME_TIME));

                msg.setId(id);
                msg.setFrom(from);
                msg.setUserId(userid);
                msg.setMtext(mtext);
                msg.setGroupId(groupid);
                msg.setGroupName(groupname);
                msg.setTime(time);
                msgs.add(msg);
            }
            cursor.close();
        }
        return msgs;
    }

    /**
     * 删除草稿
     * @param from
     */
    synchronized public void deleteDraft(String from){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if(db.isOpen()){
            db.delete(DraftDao.TABLE_NAME, DraftDao.COLUMN_NAME_FROM + " = ?", new String[]{from});
        }
    }

    synchronized int getDraftUnreadNotifyCount(){
        int count = 0;
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        if(db.isOpen()){
            Cursor cursor = db.rawQuery("select " + DraftDao.COLUMN_NAME_UNREAD_MSG_COUNT + " from " + DraftDao.TABLE_NAME, null);
            if(cursor.moveToFirst()){
                count = cursor.getInt(0);
            }
            cursor.close();
        }
        return count;
    }

    synchronized void setDraftUnreadNotifyCount(int count){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if(db.isOpen()){
            ContentValues values = new ContentValues();
            values.put(DraftDao.COLUMN_NAME_UNREAD_MSG_COUNT, count);

            db.update(DraftDao.TABLE_NAME, values, null,null);
        }
    }


    /**
     * 保存 Translate
     * @param
     * @return  返回这条Translate在db中的id
     */
    public synchronized Integer saveTranslate(Translate message){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        int id = -1;
        if(db.isOpen()){
            ContentValues values = new ContentValues();
            values.put(TranslateDao.COLUMN_NAME_FROMID, message.getFromid());
            values.put(TranslateDao.COLUMN_NAME_MFROM, message.getMfrom());
            values.put(TranslateDao.COLUMN_NAME_MTO, message.getMto());
            values.put(TranslateDao.COLUMN_NAME_DSC, message.getDst());
            values.put(TranslateDao.COLUMN_NAME_SRC, message.getSrc());
            values.put(TranslateDao.COLUMN_NAME_MSGID, message.getMsgid());
            values.put(TranslateDao.COLUMN_NAME_TIME, message.getTime());
            db.insert(TranslateDao.TABLE_NAME, null, values);

            Cursor cursor = db.rawQuery("select last_insert_rowid() from " + DraftDao.TABLE_NAME,null);
            if(cursor.moveToFirst()){
                id = cursor.getInt(0);
            }

            cursor.close();
        }
        return id;
    }

    /**
     * 更新 Translate
     * @param msgId
     * @param values
     */
    synchronized public void updateTranslate(String msgId,ContentValues values){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if(db.isOpen()){
            db.update(TranslateDao.TABLE_NAME, values, TranslateDao.COLUMN_NAME_MSGID + " = ?", new String[]{msgId});
        }
    }

    /**
     * 获取 Translate
     * @return
     */
    synchronized public Translate getTranslate(String mid){
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Translate msg = new Translate();
        if(db.isOpen()){
//            Cursor cursor = db.rawQuery("select * from " + DraftDao.TABLE_NAME + " desc", new String[]{aFrom});
            Cursor cursor = db.rawQuery(
                    "select * from " + TranslateDao.TABLE_NAME + " where " + TranslateDao.COLUMN_NAME_MSGID + "=?",
                    new String[]{mid});
            while(cursor.moveToNext()){
                int id = cursor.getInt(cursor.getColumnIndex(DraftDao.COLUMN_NAME_ID));
                String fromid = cursor.getString(cursor.getColumnIndex(TranslateDao.COLUMN_NAME_FROMID));
                String mfrom = cursor.getString(cursor.getColumnIndex(TranslateDao.COLUMN_NAME_MFROM));
                String mto = cursor.getString(cursor.getColumnIndex(TranslateDao.COLUMN_NAME_MTO));
                String msgid = cursor.getString(cursor.getColumnIndex(TranslateDao.COLUMN_NAME_MSGID));
                String src = cursor.getString(cursor.getColumnIndex(TranslateDao.COLUMN_NAME_SRC));
                String dsc = cursor.getString(cursor.getColumnIndex(TranslateDao.COLUMN_NAME_DSC));
                long time = cursor.getLong(cursor.getColumnIndex(TranslateDao.COLUMN_NAME_TIME));

                msg.setId(id);
                msg.setFromid(fromid);
                msg.setMfrom(mfrom);
                msg.setMto(mto);
                msg.setDst(dsc);
                msg.setSrc(src);
                msg.setMsgid(msgid);
                msg.setTime(time);


            }
            cursor.close();
        }
        return msg;
    }
    synchronized public List<Translate> getTranslateList(){
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        List<Translate> msgs = new ArrayList<Translate>();
        if(db.isOpen()){
            Cursor cursor = db.rawQuery("select * from " + TranslateDao.TABLE_NAME + " desc",null);
            while(cursor.moveToNext()){
                Translate msg = new Translate();
                int id = cursor.getInt(cursor.getColumnIndex(TranslateDao.COLUMN_NAME_ID));
                String fromid = cursor.getString(cursor.getColumnIndex(TranslateDao.COLUMN_NAME_FROMID));
                String mfrom = cursor.getString(cursor.getColumnIndex(TranslateDao.COLUMN_NAME_MFROM));
                String mto = cursor.getString(cursor.getColumnIndex(TranslateDao.COLUMN_NAME_MTO));
                String msgid = cursor.getString(cursor.getColumnIndex(TranslateDao.COLUMN_NAME_MSGID));
                String src = cursor.getString(cursor.getColumnIndex(TranslateDao.COLUMN_NAME_SRC));
                String dsc = cursor.getString(cursor.getColumnIndex(TranslateDao.COLUMN_NAME_DSC));
                long time = cursor.getLong(cursor.getColumnIndex(TranslateDao.COLUMN_NAME_TIME));

                msg.setId(id);
                msg.setFromid(fromid);
                msg.setMfrom(mfrom);
                msg.setMto(mto);
                msg.setMto(mto);
                msg.setDst(dsc);
                msg.setSrc(src);
                msg.setMsgid(msgid);
                msg.setTime(time);
                msgs.add(msg);
            }
            cursor.close();
        }
        return msgs;
    }

    /**
     * 删除翻译记录（个人记录）
     * @param
     */
    synchronized public void deleteTranslate(String fromid){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if(db.isOpen()){
            db.delete(TranslateDao.TABLE_NAME, TranslateDao.COLUMN_NAME_FROMID + " = ?", new String[]{fromid});
        }
    }
    /**
     * 删除翻译记录（单条记录）
     * @param
     */
    synchronized public void deleteOneTranslate(String msgid){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if(db.isOpen()){
            db.delete(TranslateDao.TABLE_NAME, TranslateDao.COLUMN_NAME_MSGID + " = ?", new String[]{msgid});
        }
    }
    synchronized int getTranslateUnreadNotifyCount(){
        int count = 0;
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        if(db.isOpen()){
            Cursor cursor = db.rawQuery("select " + TranslateDao.COLUMN_NAME_UNREAD_MSG_COUNT + " from " + TranslateDao.TABLE_NAME, null);
            if(cursor.moveToFirst()){
                count = cursor.getInt(0);
            }
            cursor.close();
        }
        return count;
    }

    synchronized void setTranslateUnreadNotifyCount(int count){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if(db.isOpen()){
            ContentValues values = new ContentValues();
            values.put(TranslateDao.COLUMN_NAME_UNREAD_MSG_COUNT, count);

            db.update(TranslateDao.TABLE_NAME, values, null,null);
        }
    }


    synchronized public void closeDB(){
        if(dbHelper != null){
            dbHelper.closeDB();
        }
        dbMgr = null;
    }
    
    
    /**
     * Save Robot list
     */
	synchronized public void saveRobotList(List<RobotUser> robotList) {
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		if (db.isOpen()) {
			db.delete(UserDao.ROBOT_TABLE_NAME, null, null);
			for (RobotUser item : robotList) {
				ContentValues values = new ContentValues();
				values.put(UserDao.ROBOT_COLUMN_NAME_ID, item.getUsername());
				if (item.getNick() != null)
					values.put(UserDao.ROBOT_COLUMN_NAME_NICK, item.getNick());
				if (item.getAvatar() != null)
					values.put(UserDao.ROBOT_COLUMN_NAME_AVATAR, item.getAvatar());
                if (item.getAvatarid() !=null)
                    values.put(UserDao.ROBOT_COLUMN_NAME_AVATARID, item.getAvatarid());
                if (item.getMobile() !=null)
                    values.put(UserDao.ROBOT_COLUMN_NAME_MOBILE,item.getMobile());
                if (item.getIsShop() !=null)
                    values.put(UserDao.ROBOT_COLUMN_NAME_ISSHOP,item.getIsShop());
                if (item.getShopId() !=null)
                    values.put(UserDao.ROBOT_COLUMN_NAME_SHOPID,item.getIsShop());
				db.replace(UserDao.ROBOT_TABLE_NAME, null, values);
			}
		}
	}
    
    /**
     * load robot list
     */
	synchronized public Map<String, RobotUser> getRobotList() {
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Map<String, RobotUser> users = null;
		if (db.isOpen()) {
			Cursor cursor = db.rawQuery("select * from " + UserDao.ROBOT_TABLE_NAME, null);
			if(cursor.getCount()>0){
				users = new HashMap<String, RobotUser>();
			};
			while (cursor.moveToNext()) {
				String username = cursor.getString(cursor.getColumnIndex(UserDao.ROBOT_COLUMN_NAME_ID));
				String nick = cursor.getString(cursor.getColumnIndex(UserDao.ROBOT_COLUMN_NAME_NICK));
				String avatar = cursor.getString(cursor.getColumnIndex(UserDao.ROBOT_COLUMN_NAME_AVATAR));
                String mobile = cursor.getString(cursor.getColumnIndex(UserDao.ROBOT_COLUMN_NAME_MOBILE));
                String avatarid = cursor.getString(cursor.getColumnIndex(UserDao.ROBOT_COLUMN_NAME_AVATARID));
                String isshop = cursor.getString(cursor.getColumnIndex(UserDao.ROBOT_COLUMN_NAME_ISSHOP));
                String shopid = cursor.getString(cursor.getColumnIndex(UserDao.ROBOT_COLUMN_NAME_SHOPID));
				RobotUser user = new RobotUser(username);
				user.setNick(nick);
				user.setAvatar(avatar);
                user.setMobile(mobile);
                user.setAvatarid(avatarid);
                user.setIsShop(isshop);
                user.setShopId(shopid);
				String headerName = null;
				if (!TextUtils.isEmpty(user.getNick())) {
					headerName = user.getNick();
				} else {
					headerName = user.getUsername();
				}
				if(Character.isDigit(headerName.charAt(0))){
					user.setInitialLetter("#");
				}else{
					user.setInitialLetter(HanziToPinyin.getInstance().get(headerName.substring(0, 1)).get(0).target
							.substring(0, 1).toUpperCase());
					char header = user.getInitialLetter().toLowerCase().charAt(0);
					if (header < 'a' || header > 'z') {
						user.setInitialLetter("#");
					}
				}
				
				users.put(username, user);
			}
			cursor.close();
		}
		return users;
	}
    
    
    
}